package com.zdsoft.crm.dao.clue;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.zdsoft.crm.entity.Clue;
import com.zdsoft.crm.entity.ClueSource;
import com.zdsoft.crm.vo.clue.ClueListVo;
public interface IClueMapper {

	@Select("SELECT c.*,user_charge.username chargeName,user_create.username createName  FROM("
			+ " SELECT A.*,ROWNUM RN FROM  (SELECT * FROM CLUE ) A WHERE ROWNUM<=#{endIndex})  c "
			+ " left join SYS_USER user_charge on c.CHARGE_USER_ID = user_charge.id "
			+ "  left join SYS_USER user_create on c.CREATE_USER_ID = user_create.id "
			+ "   WHERE RN > #{startIndex}"
			+ "    and CHARGE_USER_ID is not null")
	List<ClueListVo> pageList(@Param("startIndex") Integer startIndex, @Param("endIndex") Integer endIndex);
	
	
	@Select("<script>"
			+ " SELECT c.*,user_charge.username chargeName,user_create.username createName  FROM   (       "
			+ "  	select A.* ,ROWNUM rn"
			+ "		from ("
			+ "				select *   from CLUE"
			+ " 				<choose>  "
			+ " 					<when test='CriteriaType==\"COMPANY_NAME\"'>  "
			+ "    						  where COMPANY_NAME like '%'||#{content}||'%'  "
            + "						 </when>  "
            + "						<when test='CriteriaType==\"CONTACT_NAME\"'>  "
           	+ "    						 where CONTACT_NAME like '%'||#{content}||'%'  "
            + " 					</when>  "
            + "					</choose>  "
			+ "				) A"
			+ "		<if test='endIndex != null and endIndex!=\"\"' >"
			+ " 			<![CDATA[where ROWNUM <=#{endIndex}]]>"
			+ "		</if>  ) c"
			+ " 	left join SYS_USER user_charge on c.CHARGE_USER_ID = user_charge.id "
			+ " 	left join SYS_USER user_create on c.CREATE_USER_ID = user_create.id "
			+ " <where>"
			+ " <if test='startIndex != null and startIndex!=\"\"' >"
			+ " 		AND rn > #{startIndex}"
			+ " </if>"
			+ " <if test='1==1' >"
			+ " 		AND CHARGE_USER_ID is not null"
			+ " </if>"
			+ " </where> "
			+ " <choose>  "
            + "<when test='CriteriaType==\"chargeName\"'>  "
           	+ "     AND user_charge.username  like '%'||#{content}||'%'  "
            + " </when>  "
            + "<when test='CriteriaType==\"createName\"'>  "
           	+ "     AND user_create.username  like '%'||#{content}||'%' "
            + " </when>  "
            + "</choose>  "
			+"</script>")
	List<ClueListVo> pageListWhere(@Param("startIndex") Integer startIndex, @Param("endIndex") Integer endIndex,@Param("CriteriaType") String CriteriaType,@Param("content") String content);
	
	@Select("<script>"
			+ " SELECT count(c.id)  FROM   (       "
			+ "  	select A.* ,ROWNUM rn"
			+ "		from ("
			+ "				select *   from CLUE"
			+ " 				<choose>  "
			+ " 					<when test='CriteriaType==\"COMPANY_NAME\"'>  "
			+ "    						  where COMPANY_NAME like '%'||#{content}||'%'  "
            + "						 </when>  "
            + "						<when test='CriteriaType==\"CONTACT_NAME\"'>  "
           	+ "    						 where CONTACT_NAME like '%'||#{content}||'%'  "
            + " 					</when>  "
            + "					</choose>  "
			+ "				) A"
			+ "		  ) c"
			+ " 	left join SYS_USER user_charge on c.CHARGE_USER_ID = user_charge.id "
			+ " 	left join SYS_USER user_create on c.CREATE_USER_ID = user_create.id "
			+ " <where>"
			+ " <if test='1==1' >"
			+ " 		AND CHARGE_USER_ID is not null"
			+ " </if>"
			+ " </where> "
			+ " <choose>  "
            + "<when test='CriteriaType==\"chargeName\"'>  "
           	+ "     AND user_charge.username  like '%'||#{content}||'%'  "
            + " </when>  "
            + "<when test='CriteriaType==\"createName\"'>  "
           	+ "     AND user_create.username  like '%'||#{content}||'%' "
            + " </when>  "
            + "</choose>  "
			+"</script>")
	Long totalCount(@Param("CriteriaType") String CriteriaType,@Param("content") String content);
	
	@Select("select * from CLUE where id = #{id}")
	Clue getClueInfo(String id);
	
	/**
	 * 删除
	 * @param id
	 * @return
	 */
	@Delete("delete from CLUE where id = #{id}")
	public int delete(String id);
	
	@Insert("insert into CLUE(COMPANY_NAME,CONTACT_NAME,JOB,SEX,PHONE,EMAIL"
			+ ",NEXT_CONTACT_TIME,NEXT_CONTACT_INFO,REMARK,CHARGE_USER_ID"
			+ ",ADDRESS_INFO,CREATE_USER_ID,CLUE_SOURCE_ID) "
			+ "values(#{company_name},#{contact_name},#{job},#{sex},#{phone}"
			+ ",#{email},#{next_contact_time},#{next_contact_info}"
			+ ",#{remark,jdbcType=BLOB},#{charge_user_id},#{address_info}"
			+ ",#{create_user_id},#{clue_source_id})")
	public int save(Clue clue);
	
	
	
	@Select("select * from CLUE_SOURCE where ID = #{id}")
	ClueSource getClueSource(String id);
	
	@Select("select * from CLUE")
	List<Clue> getClueAll();
	
	@Select("select * from CLUE_SOURCE")
	List<ClueSource> getClueSourceAll();
	
	@Select("select USERNAME from SYS_USER where ID = #{id}")
	String getUserName(String id);
	
	@Select("select id from SYS_USER where username = #{username,jdbcType=VARCHAR}")
	String getUserId(String username);
	
	@Update("update CLUE set COMPANY_NAME=#{company_name},CONTACT_NAME=#{contact_name},"
			+ "  JOB=#{job},SEX=#{sex},"
			+ "  PHONE=#{phone},EMAIL=#{email},"
			+ "  NEXT_CONTACT_TIME=#{next_contact_time},NEXT_CONTACT_INFO=#{next_contact_info},"
			+ "  REMARK=#{remark,jdbcType=BLOB},CHARGE_USER_ID=#{charge_user_id},"
			+ "  ADDRESS_INFO=#{address_info},CREATE_USER_ID=#{create_user_id},"
			+ "  CLUE_SOURCE_ID=#{clue_source_id}"
			+ " where id=#{id}")
	int update(Clue clue);
	
}
